﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace WikcLib.Data
{
    public class ExcelHelper
    { 
        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        /// <returns>返回一个数据集</returns>
        public DataSet ExcelToDS(string Path)
        {
            OleDbConnection OleConn = new OleDbConnection();
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1';";
                OleConn.ConnectionString = strConn;
                OleConn.Open();
                String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等   

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                OleConn.Close();
            }
        }

        /// <summary>
        /// 写入Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        public bool SaveFP2toExcel(string Path)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
                //cmd.ExecuteNonQuery ();
                //for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
                //{
                //    if (fp2.Sheets[0].Cells[i, 0].Text != "")
                //    {
                //        cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
                //         fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
                //         "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
                //        cmd.ExecuteNonQuery();
                //    }
                //}
                conn.Close();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
            return false;
        }

        /// <summary>
        /// 写入Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        public bool SaveFP2toExcel(string Path, string strCmd)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
                //cmd.ExecuteNonQuery ();

                cmd.CommandText = strCmd;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
        }
    }
}
